/*=========================================================================
create_tts_broker_package.plb - Created by Dave Herrington, DARC Corporation


Copyright (c) 2009, Dave Herrington, DARC Corporation and Proofspace, Inc.
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:

- Redistributions of source code must retain the above copyright notice,
  this list of conditions and the following disclaimer.

- Redistributions in binary form must reproduce the above copyright notice,
  this list of conditions and the following disclaimer in the documentation
  and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.


Comments:

This package is contains the package body for trusted timestamping
procedures.  This package should not be executable outside of the TTS_ADMIN
schema.  Run this script connected to the TTS_ADMIN schema, after creating
the package specification.


Usage:

sqlplus TTS_ADMIN/<TTS_ADMIN Password> @create_tts_broker_package.plb


History:

08/06/09 D.Herrington - Created. 
08/18/09 D.Herrington - Added Changed_By handling.
08/18/09 D.Herrington - Changed update and delete handling.
09/20/09 D.Herrington - Changed to concat old & new values for hashing.

===========================================================================*/

CREATE OR REPLACE PACKAGE BODY tts_admin.tts_broker AS

FUNCTION hash_md5 (
    in_message IN VARCHAR2
)
RETURN VARCHAR2 DETERMINISTIC
AS
BEGIN
    RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(
         DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(in_message), 2)));
END;

FUNCTION hash_sha128 (
    in_message IN VARCHAR2
)
RETURN VARCHAR2 DETERMINISTIC
AS
BEGIN
    RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(
         DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW(in_message), 3)));
END;

FUNCTION gethash_java (
    in_message IN VARCHAR2,
    in_alg IN VARCHAR2
)
RETURN RAW DETERMINISTIC
AS LANGUAGE JAVA NAME
    'HashLib.gethash(java.lang.String, java.lang.String) return java.lang.String';

FUNCTION hash_sha256 (
    in_message IN VARCHAR2
)
RETURN VARCHAR2 DETERMINISTIC
AS
BEGIN
    RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(
         gethash_java(in_message, 'SHA-256')));
END;

FUNCTION hash_sha512 (
    in_message IN VARCHAR2
)
RETURN VARCHAR2 DETERMINISTIC
AS
BEGIN
    RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(
         gethash_java(in_message, 'SHA-512')));
END;

PROCEDURE who_called_me (
	p_owner		OUT VARCHAR2,
	p_name		OUT VARCHAR2,
	p_lineno	OUT NUMBER,
	p_caller_t	OUT VARCHAR2 ) IS

    v_call_stack  	VARCHAR2(4096) DEFAULT dbms_utility.format_call_stack;
    v_n           	NUMBER;
    v_found_stack 	BOOLEAN default FALSE;
    v_line        	VARCHAR2(255);
    v_cnt         	NUMBER := 0;

BEGIN

    LOOP
        v_n := INSTR( v_call_stack, CHR(10) );
        EXIT WHEN ( v_cnt = 3 or v_n IS NULL OR v_n = 0 );

        v_line := SUBSTR( v_call_stack, 1, v_n-1 );
        v_call_stack := SUBSTR( v_call_stack, v_n+1 );

        IF ( NOT v_found_stack ) THEN
            IF ( v_line LIKE '%handle%number%name%' ) THEN
                v_found_stack := TRUE;
            END IF;
        ELSE
            v_cnt := v_cnt + 1;
            -- v_cnt = 1 is ME
            -- v_cnt = 2 is MY Caller
            -- v_cnt = 3 is Their Caller
            IF ( v_cnt = 3 ) THEN
                p_lineno := TO_NUMBER(SUBSTR( v_line, 13, 6 ));
                v_line   := SUBSTR( v_line, 21 );
                IF ( v_line LIKE 'pr%' ) THEN
                    v_n := LENGTH( 'procedure ' );
                ELSIF ( v_line LIKE 'fun%' ) THEN
                    v_n := LENGTH( 'function ' );
                ELSIF ( v_line LIKE 'package body%' ) THEN
                    v_n := LENGTH( 'package body ' );
                ELSIF ( v_line LIKE 'pack%' ) THEN
                    v_n := LENGTH( 'package ' );
                ELSIF ( v_line LIKE 'anonymous%' ) THEN
                    v_n := LENGTH( 'anonymous block ' );
                ELSE
                    v_n := null;
                END IF;
                IF ( v_n IS NOT NULL ) THEN
                   p_caller_t := LTRIM(RTRIM(UPPER(SUBSTR( v_line, 1, v_n-1 ))));
                ELSE
                   p_caller_t := 'TRIGGER';
                END IF;

                v_line := SUBSTR( v_line, NVL(v_n,1) );
                v_n := INSTR( v_line, '.' );
--                p_owner := LTRIM(RTRIM(SUBSTR( v_line, 1, v_n-1 )));
                p_owner := LTRIM(RTRIM(SUBSTR( v_line, 5, v_n-1 ))); /* Was putting "20  " in front of name */
                p_name  := LTRIM(RTRIM(SUBSTR( v_line, v_n+1 )));
            END IF;
        END IF;
    END LOOP;
END who_called_me;

PROCEDURE log_change (
	p_change_type	IN	VARCHAR2,
	p_changed_by	IN	VARCHAR2,
	p_table_id	IN	NUMBER,
	p_record_id	IN	VARCHAR2,
	p_col01_old	IN	VARCHAR2,
	p_col01_new	IN	VARCHAR2,
	p_col02_old	IN	VARCHAR2,
	p_col02_new	IN	VARCHAR2,
	p_col03_old	IN	VARCHAR2,
	p_col03_new	IN	VARCHAR2,
	p_col04_old	IN	VARCHAR2,
	p_col04_new	IN	VARCHAR2,
	p_col05_old	IN	VARCHAR2,
	p_col05_new	IN	VARCHAR2,
	p_col06_old	IN	VARCHAR2,
	p_col06_new	IN	VARCHAR2,
	p_col07_old	IN	VARCHAR2,
	p_col07_new	IN	VARCHAR2,
	p_col08_old	IN	VARCHAR2,
	p_col08_new	IN	VARCHAR2,
	p_col09_old	IN	VARCHAR2,
	p_col09_new	IN	VARCHAR2,
	p_col10_old	IN	VARCHAR2,
	p_col10_new	IN	VARCHAR2,
	p_col11_old	IN	VARCHAR2,
	p_col11_new	IN	VARCHAR2,
	p_col12_old	IN	VARCHAR2,
	p_col12_new	IN	VARCHAR2,
	p_col13_old	IN	VARCHAR2,
	p_col13_new	IN	VARCHAR2,
	p_col14_old	IN	VARCHAR2,
	p_col14_new	IN	VARCHAR2,
	p_col15_old	IN	VARCHAR2,
	p_col15_new	IN	VARCHAR2) AS

   req			UTL_HTTP.REQ;
   resp			UTL_HTTP.RESP;
   value		VARCHAR2(1024);
   v_param		VARCHAR2(4000);  /* Post Parameters */
   v_request_xml	VARCHAR2(4000);
   v_param_length 	NUMBER;

   /* Concatenated data to be hashed (might try a CLOB data type to see what happens.) */
   v_data_values        VARCHAR2(4000);

   r_sysparams		tts_admin.tts_system_parameters%ROWTYPE;
   r_changelog		tts_admin.tts_change_log%ROWTYPE;

BEGIN

   /* Ensure trusted timestamp field is NULL. */
   r_changelog.trusted_timestamp := NULL;

   /* Concatenate data values that will be used to create hash. */

   v_data_values :=
         p_col01_old||p_col01_new||p_col02_old||p_col02_new||p_col03_old||
         p_col03_new||p_col04_old||p_col04_new||p_col05_old||p_col05_new||
         p_col06_old||p_col06_new||p_col07_old||p_col07_new||p_col08_old||
         p_col08_new||p_col09_old||p_col09_new||p_col10_old||p_col10_new||
         p_col11_old||p_col11_new||p_col12_old||p_col12_new||p_col13_old||
         p_col13_new||p_col14_old||p_col14_new||p_col15_old||p_col15_new;

   /* Get System Parameters */
   SELECT *
   INTO   r_sysparams
   FROM   tts_admin.tts_system_parameters;

   CASE
      WHEN r_sysparams.hash_algorithm_type = 'SHA-1' THEN

         /* Create a SHA-1 Hash of the User Data with built-in functions
            (Unfortunately, 10g does not have SHA-2 built-in).
            Use SHA-1 if custom p_hash package is not installed and working. */
         r_changelog.base64_hash_digest := UTL_RAW.cast_to_varchar2(
                                UTL_ENCODE.BASE64_ENCODE(
                                    DBMS_CRYPTO.Hash( src => UTL_RAW.cast_to_raw(v_data_values), 
                                                      typ => DBMS_CRYPTO.hash_sh1)));

      WHEN r_sysparams.hash_algorithm_type = 'SHA-256' THEN

         /* Create a SHA-256 crypto hash of the User data in Base64 format
            to send in Trusted Timestamp request. Requires custom p_hash package. 
            Use this section if custom p_hash package is installed and working. */
         r_changelog.base64_hash_digest := hash_sha256(v_data_values);

      WHEN r_sysparams.hash_algorithm_type = 'SHA-512' THEN

         /* Create a SHA-512 crypto hash of the User data in Base64 format
            to send in Trusted Timestamp request. Requires custom p_hash package.
            Use this section if/when Trusted Timestamp Issuer supports SHA-512. */
         r_changelog.base64_hash_digest := hash_sha512(v_data_values);

   END CASE;

   /* TO-DO: Handle more Trusted Timestamps than just ProofSpace Proofmarks. */
   IF UPPER(r_sysparams.tts_issuer) = 'PROOFSPACE' THEN

      v_param := 'verifySignatures=false'||chr(10)||
             'storeOnServer=false'||chr(10)||
             'returnReference=false'||chr(10)||
             'data='||chr(10)||
             '<ProofMarkRequest version="1.0">'||chr(10)||
             '  <Data>'|| p_record_id ||'</Data>'||chr(10)||
             '  <Digest>'|| r_changelog.base64_hash_digest || '</Digest>'||chr(10)||
             '</ProofMarkRequest>';

      v_param_length := length(v_param);

      req := UTL_HTTP.BEGIN_REQUEST (url=> r_sysparams.tts_issuer_url,
                                     method => 'POST',
                                     http_version => UTL_HTTP.HTTP_VERSION_1_1);

      UTL_HTTP.SET_HEADER (r      =>  req,
                           name   =>  'Content-Type',
                           value  =>  'text/plain');

      UTL_HTTP.SET_HEADER (r      =>   req,
                           name   =>   'Content-Length',
                           value  =>   v_param_length);

      UTL_HTTP.SET_HEADER (r      =>   req,
                           name   =>   'Cache-Control',
                           value  =>   'no-cache');

      UTL_HTTP.SET_HEADER (r      =>   req,
                           name   =>   'Pragma',
                           value  =>   'no-cache');

      UTL_HTTP.SET_HEADER (r      =>   req,
                           name   =>   'User-Agent',
                           value  =>   'Java/1.5.0_11');

      UTL_HTTP.SET_HEADER (r      =>   req,
                           name   =>   'Host',
                           value  =>   r_sysparams.tts_server_hostname);

      UTL_HTTP.SET_HEADER (r      =>   req,
                           name   =>   'Accept',
                           value  =>   'text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2');

      UTL_HTTP.SET_HEADER (r      =>   req,
                           name   =>   'Connection',
                           value  =>   'keep-alive');

      UTL_HTTP.WRITE_TEXT (r      =>   req,
                           data   =>   v_param);

      resp := UTL_HTTP.GET_RESPONSE(req);

      /* Read Trusted Timestamp response into the CLOB column variable. */
      UTL_HTTP.READ_TEXT(resp, r_changelog.trusted_timestamp);

      UTL_HTTP.END_RESPONSE(resp);

     /* ELSE TSA Not Supported */

   END IF;

   /* Get next change log sequence number and sysdate. */
   SELECT tts_change_log_seq.NEXTVAL, CURRENT_TIMESTAMP
   INTO   r_changelog.change_id, r_changelog.change_datetime
   FROM   dual;

   /* Set other values for insert into the change log. */
   r_changelog.record_id := p_record_id;
   r_changelog.change_type := p_change_type;
   r_changelog.changed_by := p_changed_by;
   r_changelog.table_id := p_table_id;
   r_changelog.record_id := p_record_id;
   r_changelog.col01_old := p_col01_old;
   r_changelog.col01_new := p_col01_new;
   r_changelog.col02_old := p_col02_old;
   r_changelog.col02_new := p_col02_new;
   r_changelog.col03_old := p_col03_old;
   r_changelog.col03_new := p_col03_new;
   r_changelog.col04_old := p_col04_old;
   r_changelog.col04_new := p_col04_new;
   r_changelog.col05_old := p_col05_old;
   r_changelog.col05_new := p_col05_new;
   r_changelog.col06_old := p_col06_old;
   r_changelog.col06_new := p_col06_new;
   r_changelog.col07_old := p_col07_old;
   r_changelog.col07_new := p_col07_new;
   r_changelog.col08_old := p_col08_old;
   r_changelog.col08_new := p_col08_new;
   r_changelog.col09_old := p_col09_old;
   r_changelog.col09_new := p_col09_new;
   r_changelog.col10_old := p_col10_old;
   r_changelog.col10_new := p_col10_new;
   r_changelog.col11_old := p_col11_old;
   r_changelog.col11_new := p_col11_new;
   r_changelog.col12_old := p_col12_old;
   r_changelog.col12_new := p_col12_new;
   r_changelog.col13_old := p_col13_old;
   r_changelog.col13_new := p_col13_new;
   r_changelog.col14_old := p_col14_old;
   r_changelog.col14_new := p_col14_new;
   r_changelog.col15_old := p_col15_old;
   r_changelog.col15_new := p_col15_new;
   r_changelog.validated_flag := 'N';
   r_changelog.last_validation_date := NULL;

  /* Insert change record into log table. */
  INSERT INTO tts_change_log VALUES r_changelog;

EXCEPTION

  WHEN OTHERS THEN NULL;

END log_change;

PROCEDURE validate_changes (
	p_min_change_id	IN	NUMBER	DEFAULT 0,
        p_max_change_id IN	NUMBER	DEFAULT 99999999999999999999,
        p_min_change_date IN	DATE	DEFAULT '01-JAN-0001',
        p_max_change_date IN	DATE	DEFAULT '31-DEC-9999') AS

   CURSOR c_changelog IS
      SELECT *
      FROM tts_change_log
      WHERE change_id BETWEEN p_min_change_id AND p_max_change_id
      AND   change_datetime BETWEEN p_min_change_date AND p_max_change_date
      FOR UPDATE;

   req				UTL_HTTP.REQ;
   resp				UTL_HTTP.RESP;
   value			VARCHAR2(1024);
   v_param			VARCHAR2(4000);  /* Post Parameters */
   v_request_xml		VARCHAR2(4000);
   v_param_length 		NUMBER;
   v_base64_hash_digest		VARCHAR2(100);  /* Holds a rehash of the original values */
   v_tts_hash_digest		VARCHAR2(100);  /* Holds the hash digest extracted from the trusted timestamp */
   v_tts_timestamp_str		VARCHAR2(100);  /* Holds the trusted timestamp as a string */	
   v_tts_timestamp		TIMESTAMP WITH TIME ZONE;  /* Holds the trusted timestamp date/time timestamp */
   v_verification_report	CLOB;
   v_validation_error_message	VARCHAR2(1000);
   v_validated_flag		CHAR(1);

  /* Concatenated data to be hashed (might try a CLOB data type to see what happens.) */
   v_data_values        VARCHAR2(4000);

   r_sysparams		tts_admin.tts_system_parameters%ROWTYPE;
   r_changelog		tts_admin.tts_change_log%ROWTYPE;

BEGIN

   /* Get System Parameters */
   SELECT *
   INTO   r_sysparams
   FROM   tts_admin.tts_system_parameters;

   /* Get Change Log Records */
   FOR r_changelog In c_changelog LOOP

      /* Validate the Trusted Timestamp */

      v_data_values :=
         r_changelog.col01_old||r_changelog.col01_new||r_changelog.col02_old||r_changelog.col02_new||r_changelog.col03_old||
         r_changelog.col03_new||r_changelog.col04_old||r_changelog.col04_new||r_changelog.col05_old||r_changelog.col05_new||
         r_changelog.col06_old||r_changelog.col06_new||r_changelog.col07_old||r_changelog.col07_new||r_changelog.col08_old||
         r_changelog.col08_new||r_changelog.col09_old||r_changelog.col09_new||r_changelog.col10_old||r_changelog.col10_new||
         r_changelog.col11_old||r_changelog.col11_new||r_changelog.col12_old||r_changelog.col12_new||r_changelog.col13_old||
         r_changelog.col13_new||r_changelog.col14_old||r_changelog.col14_new||r_changelog.col15_old||r_changelog.col15_new;
   
      CASE
         WHEN r_sysparams.hash_algorithm_type = 'SHA-1' THEN

            /* Create a SHA-1 Hash of the User Data with built-in functions
               (Unfortunately, 10g does not have SHA-2 built-in).
               Use SHA-1 if custom p_hash package is not installed and working. */
            v_base64_hash_digest := UTL_RAW.cast_to_varchar2(
                                   UTL_ENCODE.BASE64_ENCODE(
                                       DBMS_CRYPTO.Hash( src => UTL_RAW.cast_to_raw(v_data_values), 
                                                         typ => DBMS_CRYPTO.hash_sh1)));

         WHEN r_sysparams.hash_algorithm_type = 'SHA-256' THEN

            /* Create a SHA-256 crypto hash of the User data in Base64 format
               to send in Trusted Timestamp request. Requires custom p_hash package. 
               Use this section if custom p_hash package is installed and working. */
            v_base64_hash_digest := hash_sha256(v_data_values);

         WHEN r_sysparams.hash_algorithm_type = 'SHA-512' THEN

            /* Create a SHA-512 crypto hash of the User data in Base64 format
               to send in Trusted Timestamp request. Requires custom p_hash package.
               Use this section if/when ProofMark Issuer supports SHA-512. */
            v_base64_hash_digest := hash_sha512(v_data_values);

      END CASE;

      /* TO-DO: Handle more Trusted Timestamps than just ProofSpace Proofmarks. */
      IF UPPER(r_sysparams.tts_issuer) = 'PROOFSPACE' THEN

         v_param := 'includeData=false'||chr(10)||
                'level=1'||chr(10)||
                'data='||chr(10)||
                r_changelog.trusted_timestamp;

         v_param_length := length(v_param);

         req := UTL_HTTP.BEGIN_REQUEST (url=> r_sysparams.tts_verifier_url,
                                        method => 'POST',
                                        http_version => UTL_HTTP.HTTP_VERSION_1_1);

         UTL_HTTP.SET_HEADER (r      =>  req,
                              name   =>  'Content-Type',
                              value  =>  'text/plain');

         UTL_HTTP.SET_HEADER (r      =>   req,
                              name   =>   'Content-Length',
                              value  =>   v_param_length);

         UTL_HTTP.SET_HEADER (r      =>   req,
                              name   =>   'Cache-Control',
                              value  =>   'no-cache');

         UTL_HTTP.SET_HEADER (r      =>   req,
                              name   =>   'Pragma',
                              value  =>   'no-cache');

         UTL_HTTP.SET_HEADER (r      =>   req,
                              name   =>   'User-Agent',
                              value  =>   'Java/1.5.0_11');

         UTL_HTTP.SET_HEADER (r      =>   req,
                              name   =>   'Host',
                              value  =>   r_sysparams.tts_server_hostname);

         UTL_HTTP.SET_HEADER (r      =>   req,
                              name   =>   'Accept',
                              value  =>   'text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2');

         UTL_HTTP.SET_HEADER (r      =>   req,
                              name   =>   'Connection',
                              value  =>   'keep-alive');

         UTL_HTTP.WRITE_TEXT (r      =>   req,
                              data   =>   v_param);

         resp := UTL_HTTP.GET_RESPONSE(req);

         /* Read Trusted Timestamp Validation response into the CLOB column variable. */
         UTL_HTTP.READ_TEXT(resp, v_verification_report);

         UTL_HTTP.END_RESPONSE(resp);

         v_validation_error_message := NULL;
         v_validated_flag := 'Y';

         /* If data values do not rehash to the original hash value recorded in the change log, then report an error. */ 
         IF r_changelog.base64_hash_digest != v_base64_hash_digest THEN
            v_validated_flag := 'N';
            v_validation_error_message := 'Data value rehash does not match original Hash Digest. ';
         END IF;

         /* Extract Hash Digest out of Trusted Timestamp XML. */
         v_tts_hash_digest := 
             SUBSTR( r_changelog.trusted_timestamp, INSTR( r_changelog.trusted_timestamp, '<Digest>') + 8,
                INSTR( r_changelog.trusted_timestamp, '</Digest>') - INSTR( r_changelog.trusted_timestamp, '<Digest>') - 8 );

         /* If Hash Digest in the Trusted Timestamp does not match the Hash Digest recorded in the Change Log, then report an error. */
         IF r_changelog.base64_hash_digest != v_tts_hash_digest THEN
            v_validated_flag := 'N';
            v_validation_error_message := v_validation_error_message || 'Hash Digest in the Trusted Timestamp does not match original Hash Digest. ';
         END IF;

         /* Extract Timestamp out of Trusted Timestamp into a Timestamp variable WITH TIMEZONE.

            Examples that need to be handled from ProofSpace Proofmarks:
              <Timestamp accuracyMs="841">2009-07-27 12.09.59.907 UTC-06:00</Timestamp>

              <Timestamp accuracyMs="1184">2009-08-03 17.30.21.040 UTC-05:00 (CDT)</Timestamp>
              <Timestamp accuracyMs="2121">2009-08-12 11.24.09.609 UTC-06:00</Timestamp>
         */

         /* Just grab the portion of the Trusted Timestamp that contains the timestamp, for processing. */
         v_tts_timestamp_str := SUBSTR( r_changelog.trusted_timestamp, INSTR( r_changelog.trusted_timestamp, '<Timestamp accuracy'),
                 INSTR( r_changelog.trusted_timestamp, '</Timestamp>') - INSTR( r_changelog.trusted_timestamp, '<Timestamp accuracy') );

         /* Convert the string timestamp datatype into an Oracle timestamp with Time Zone datatype */
         v_tts_timestamp := 
             FROM_TZ(
                    /* Timestamp is first parameter */ 
                    TO_TIMESTAMP(
                                SUBSTR(v_tts_timestamp_str,
                                       INSTR(v_tts_timestamp_str,'>') + 1, /* Start Position: just after first XML tag */
                                       INSTR(v_tts_timestamp_str,'UTC') - INSTR(v_tts_timestamp_str,'>') - 1 /* Length */
                                      ),
                                'YYYY-MM-DD HH24.MI.SSXFF3' /* Format Mask */
                                ),
                    /* Time Zone is second parameter */
                    SUBSTR(v_tts_timestamp_str,
                           INSTR( v_tts_timestamp_str, 'UTC') + 3, /* Start Position: just after "UTC" */
                           6 /* Length of the Time Zone, including the + or - sign */ 
                           )
                    ); 


         /* If Trusted Timestamp date/time timestamp is much newer than the change date/time, then report an error. */
         IF v_tts_timestamp > r_changelog.change_datetime + r_sysparams.clock_diff_tolerance_interval THEN /* Tolerated clock differences */
            v_validated_flag := 'N';
            v_validation_error_message := v_validation_error_message || 'Trusted Timestamp date/time timestamp is newer than the original Change timestamp than the tolerance setting allows. ';
         END IF;

         /* Extract Trusted Timestamp Signature verification status out of verification report and report error if not found. */
         IF INSTR( v_verification_report, '<Signature verified="true"/>') = 0 THEN
            v_validated_flag := 'N';
            v_validation_error_message := v_validation_error_message || 'Trusted Timestamp Signature failed verification. ';
         END IF;

         /* Extract Proofmark Interval verification status out of ProofMark verification report and report error if not found. */
         IF INSTR( v_verification_report, '<Interval verified="true"/>') = 0 THEN
            v_validated_flag := 'N';
            v_validation_error_message := v_validation_error_message || 'ProofMark Interval failed verification. ';
         END IF;

         /* Extract ProofMark Signature verification status out of ProofMark verification report and report error if not found. */
         IF INSTR( v_verification_report, '<IntervalIdentitySignature verified="true"/>') = 0 THEN
            v_validated_flag := 'N';
            v_validation_error_message := v_validation_error_message || 'ProofMark Interval Identity Signature failed verification. ';

         END IF;

      /* ELSE TSA Not Supported */
      END IF;


      /* Update change record with validation info. */
      UPDATE tts_change_log
      SET    validated_flag = v_validated_flag,
             last_validation_date = SYSDATE,
             validation_error_message = v_validation_error_message,
             verification_report = v_verification_report
      WHERE CURRENT OF c_changelog;

   END LOOP;

   /* TO-DO:  Implement exception handling. */
-- EXCEPTION

--  WHEN OTHERS THEN NULL;

END validate_changes;

END tts_broker;
/